# encoding: UTF-8
# user：admin at 2018/10/18

import sqlite3
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QStandardItemModel,QStandardItem

class read2Sqlite():
    def __init__(self):
        self.conn=sqlite3.connect('db/home_db.db')

    def writeDB(self,dictData):
        try:
            cur=self.conn.cursor()
            selectSQL = "select exists(select 1 from t_ygcbxx where name=? and fdate=?) "
            value = (dictData['name'],dictData['fdate'])
            cur.execute(selectSQL,value)
            cnt = cur.fetchall()[0]

            if cnt[0] == 1:
                message='【{}】的【{}】社保数据，已经存在！'.format(dictData['name'],dictData['fdate'])
                return True,message
            else:
                sql = "insert into t_ygcbxx(name,fdate,fyanglaoje,fyiliaoje,fgongshangje,fshiyeje,fshengyuje) " \
                      "values(?,?,?,?,?,?,?)"
                values=tuple(dictData.values())
                cur.execute(sql, values)
                self.conn.commit()
                return True,'成功'

        except Exception as message:
            return False,message

    def getCbxx(self,name='',startDate='',endDate=''):
        '''
        获取参保信息
        :param name:姓名
        :param startDate:开始年月
        :param endDate: 截止年月
        :return: 值1：是否有异常；值2：model类型数据；值3：是否有数据
        '''
        headerList = ['姓名', '年月', '养老保险', '医疗保险', '工伤保险', '失业保险', '生育保险', '合计金额']
        QuerySQL = "select name,fdate,fyanglaoje,fyiliaoje,fgongshangje,fshiyeje,fshengyuje, " \
                   " fyanglaoje+fyiliaoje+fgongshangje+fshiyeje+fshengyuje as totalje " \
                   " from t_ygcbxx where 1=1"
        strNameFilter=''
        strFdateFilter=''
        nameValue=tuple()
        fdateValue=tuple()
        if name!='':
            strNameFilter=" and name=? "
            nameValue=self.str2tuple(name)

        if startDate!='':
            strFdateFilter=" and fdate>=? and fdate<=? "
            fdateValue=(startDate,endDate)

        value = nameValue+fdateValue

        QuerySQL = QuerySQL + strNameFilter + strFdateFilter

        try:
            cur = self.conn.cursor()
            cur.execute(QuerySQL,value)
            Datas=cur.fetchall()
            #print(len(Datas),len(Datas[0]))

            c=len(headerList)
            model=QStandardItemModel(2,c)
            model.clear()
            model.setHorizontalHeaderLabels(headerList)

            if Datas:
                for r,rdata in enumerate(Datas):        #行数据
                    for c,cell in enumerate(rdata):     #列数据
                        if c>=2:
                            value='{:.2f}'.format(cell)
                            item = QStandardItem(value)
                            item.setTextAlignment(Qt.AlignRight|Qt.AlignVCenter)  # 靠右对齐
                        else:
                            value=str(cell)
                            item=QStandardItem(value)
                            item.setTextAlignment(Qt.AlignCenter)       #居中对齐
                        model.setItem(r,c,item)

                return True,model
            else:
                item=QStandardItem('无数据！')
                model.setItem(0,0,item)
                return True,model

        except Exception as message:
            return False,message,False
        finally:
            cur.close()
            self.conn.close()

    def str2tuple(self,*str):
        return str